package jdbc;

import java.sql.*;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import models.*;

/**
 * Java JDBC : 后开启的需要先关闭，Statement、Connection、ResultSet都是需要关闭的
 */
public class JdbcTest {
    public static void main(String[] args) {
        Connection conn = null;
        CallableStatement cs = null;
        Savepoint sp = null;// 事务回滚点
        try {
            Class.forName("org.sqlite.JDBC");// 加载驱动(开发推荐的方式)
            /**
             * Oracle：jdbc:oracle:thin:@localhost:1521:shen
             * SqlServer：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=shen
             * MySql：jdbc:mysql://localhost:3306/shen sqlite：jdbc:sqlite:test.db
             */
            conn = DriverManager.getConnection("jdbc:sqlite:./test.db");// 获取连接
            conn.setAutoCommit(false); // 设置事务是否自动提交，开启事务

            System.out.println("**********Statement 执行sql语句************");
            Statement sm = conn.createStatement();// 创建向数据库发送sql的statement对象

            String sql = "drop table COMPANY;";
            sm.executeUpdate(sql);// 插入、修改、删除、更新，返回结果是一个整数（受影响的行数）

            sp = conn.setSavepoint();// 在这里设置事务回滚点

            sql = "CREATE TABLE COMPANY " + "(ID INT PRIMARY KEY     NOT NULL," + " NAME           TEXT    NOT NULL, "
                    + " AGE            INT     NOT NULL, " + " ADDRESS        CHAR(50), " + " SALARY         REAL)";
            sm.executeUpdate(sql);

            sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
                    + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
            sm.executeUpdate(sql);
            ResultSet keyRs = sm.getGeneratedKeys();// 获取生成的主键

            sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
            sm.executeUpdate(sql);

            String sql1 = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
                    + "VALUES (3, 'Allen', 25, 'Texas', 150300.00 );";
            String sql2 = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
                    + "VALUES (4, 'Allen', 25, 'Texas', 152000.00 );";
            sm.addBatch(sql1);
            sm.addBatch(sql2);
            sm.executeBatch();// 批量处理SQL
            sm.clearBatch();

            ResultSet rs = sm.executeQuery("SELECT * FROM COMPANY;");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println("ID = " + id);
                System.out.println("NAME = " + name);
                System.out.println("AGE = " + age);
                System.out.println("ADDRESS = " + address);
                System.out.println("SALARY = " + salary);
                System.out.println();
            }
            rs.close();
            sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
            sm.executeUpdate(sql);

            rs = sm.executeQuery("SELECT * FROM COMPANY;");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println("ID = " + id);
                System.out.println("NAME = " + name);
                System.out.println("AGE = " + age);
                System.out.println("ADDRESS = " + address);
                System.out.println("SALARY = " + salary);
                System.out.println();
            }
            rs.close();

            sql = "DELETE from COMPANY where ID=2;";
            sm.executeUpdate(sql);
            conn.commit();

            rs = sm.executeQuery("SELECT * FROM COMPANY;");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println("ID = " + id);
                System.out.println("NAME = " + name);
                System.out.println("AGE = " + age);
                System.out.println("ADDRESS = " + address);
                System.out.println("SALARY = " + salary);
                System.out.println();
            }
            rs.close();
            sm.close();

            System.out.println("**********PreparedStatement 执行sql语句************");
            /**
             * PreparedStatement 与
             * Statement，但不同的是PreparedStatement可以使用占位符,它是由占位符标识(?)需要输入数据的位置，然后再逐一填入数据。当然，PreparedStatement也可以执行没有占位符的sql语句，使用占位符后，使用setString、setInt、setFloat等方法来设置每一位的数据
             * 相对于Statement对象而言PreperedStatement可以避免SQL注入的问题
             */
            PreparedStatement st = null;
            sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (?, ?, ?, ?, ? );";
            // 向数据库发送sql语句的Preperedstatement
            st = conn.prepareStatement(sql);// 在此次传入，进行预编译

            st.setInt(1, 5);
            st.setString(2, "czj");
            st.setInt(3, 26);
            st.setString(4, "江宁");
            st.setDouble(5, 2600.00);
            st.execute();
            conn.commit();

            st = conn.prepareStatement("SELECT * FROM COMPANY;");// 在此次传入，进行预编译
            ResultSet rs1 = st.executeQuery();
            while (rs1.next()) {
                int id = rs1.getInt("id");
                String name = rs1.getString("name");
                int age = rs1.getInt("age");
                String address = rs1.getString("address");
                float salary = rs1.getFloat("salary");
                System.out.println("ID = " + id);
                System.out.println("NAME = " + name);
                System.out.println("AGE = " + age);
                System.out.println("ADDRESS = " + address);
                System.out.println("SALARY = " + salary);
                System.out.println();
            }
            rs1.close();

            st = conn.prepareStatement(sql);
            for (int i = 6; i < 15; i++) { // i=1000 2000
                st.setInt(1, i);
                st.setString(2, "czj" + i);
                st.setInt(3, 26);
                st.setString(4, "江宁");
                st.setDouble(5, 2600.00);
                st.addBatch();
            }
            st.executeBatch();
            st.clearBatch();
            conn.commit();

            st = conn.prepareStatement("SELECT * FROM COMPANY;");// 在此次传入，进行预编译
            rs1 = st.executeQuery();
            while (rs1.next()) {
                int id = rs1.getInt("id");
                String name = rs1.getString("name");
                int age = rs1.getInt("age");
                String address = rs1.getString("address");
                float salary = rs1.getFloat("salary");
                System.out.println("ID = " + id);
                System.out.println("NAME = " + name);
                System.out.println("AGE = " + age);
                System.out.println("ADDRESS = " + address);
                System.out.println("SALARY = " + salary);
                System.out.println();
            }
            rs1.close();

            System.out.println("**********prepareCall 调用存储过程************");
            /**
             * CallableStatement执行语句 CallableStatement主要用来调用存储过程(?为占位符)
             * 对于输出参数，需要使用registerOutParameter注册，第一个参数是位置，第二个参数是参数类型
             */
            // cs = conn.prepareCall("{call p_course_inser(?,?,?)}");
            // cs.setString(1, "xxxxx");
            // cs.setInt(2, 20);
            // cs.registerOutParameter(3, Types.VARCHAR);// 输出变量需要进行注册

            // cs.execute();
            // String result = cs.getString(3);// 获取输出变量返回值

            // conn.close();

            System.out.println("*********数据库连接池 DBCP*********");
            /**
             * DBCP 连接池 需要导入包：commons-dbcp.jar和commons-pool.jar
             */
            // 创建对象
            BasicDataSource ds = new BasicDataSource();
            // 配置DataSource
            ds.setDriverClassName("org.sqlite.JDBC");
            ds.setUrl("jdbc:sqlite:./test.db");
            // ds.setUsername("userName");
            // ds.setPassword("password");
            conn = ds.getConnection();// 获取连接对象
            sm = conn.createStatement();
            rs = sm.executeQuery("SELECT * FROM COMPANY;");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println("ID = " + id);
                System.out.println("NAME = " + name);
                System.out.println("AGE = " + age);
                System.out.println("ADDRESS = " + address);
                System.out.println("SALARY = " + salary);
                System.out.println();
            }
            rs.close();
            sm.close();
            conn.close();
        } catch (Exception e) {
            try {
                conn.rollback(sp);// 回滚到该事务点，即该点之前的会正常执行（sql1）
                conn.commit(); // 回滚了要记得提交,如果没有提交sql1将会自动回滚
            } catch (SQLException sqlex) {
                // TODO Auto-generated catch block
                sqlex.printStackTrace();
            }
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Opened database successfully");
    }
}
